 /*
 
This program produces Tables 3, 4, and A9


*/
 
 
 
 
 **Set directories
cd ""  /* PROJECT ROOT FOLDER */

/* DEFINE GLOBALS PATHS HERE */


global data data
global input input
global temp work
global output output
global distances "data/distances"
global disc_programs code
global disclosure code

global ver _16


use "$data/firm_country_matrix.dta", clear  //  made in make_firm_country_data_01.do

replace mne_status="FOR" if mne_status=="FO2"

merge 1:1 firmid iso3 using "$distances/aff_weighted_dist_measures_ext_export.dta"
drop if _merge==2
drop _merge

merge 1:1 firmid iso3 using "$distances/aff_weighted_dist_measures_ext_import.dta"
drop if _merge==2
drop _merge

merge 1:1 firmid iso3 using "$distances/aff_weighted_FTA_ext_import.dta"
drop if _merge == 2
drop _merge


foreach vv of varlist x_phi_i* y_phi_i*  {
	replace `vv' = 0 if `vv' == .
}


merge 1:1 firmid iso3 using "$distances/closest_affiliate_indicators.dta"
drop if _merge == 2

foreach vv in aff0_500 aff501_1000 aff1001_2000 aff2001_4000 aff4001 {
	replace `vv' = 0 if `vv' == .
}

drop _merge




gen log_num_exp = ln(1+num_exp - exporters)
gen log_num_imp = ln(1+num_imp - importers)
gen log_num_maff = ln(1+num_maff - assemblers)

gen log_imports=ln(import)
gen log_exports=ln(export)

label var log_num_exp "Log of 1+ number region export countries, excluding country"
label var log_num_imp "Log of 1+ number region import countries, excluding country"
label var log_num_maff "Log of 1+ number of region affiliate countries, excluding country"


label var region_exporters "Exporter to any other country in region"
label var region_importers "Importer from any other country in region"
label var region_assemblers "Assembly in any other country in region"


tab assemblers region_assemblers, miss
tab importers region_importers, missing
tab exporters region_exporters, miss


gen region_assm_v2 = (num_maff - assemblers) > 0

*Special variable defined for regressing importers importers_region
foreach flow in imp exp {
	gen region_`flow'orters_`flow'=1 if num_`flow'_countries_r - `flow'orters > 0
	  replace region_`flow'orters_`flow'=0 if num_`flow'_countries_r - `flow'orters <= 0
	  label variable region_`flow'orters_`flow' "`flow'orter from other countries in region"  
	  }
	  
tab region_importers_imp, missing
tab region_exporters_exp, missing

	  
label variable 	region_exporters_exp "Exporters to other countries in region"  
label variable importers "Importer from Country"
label variable exporters "Exporter to Country"




*limit to countries with data	
gen log_dist=ln(distw)
gen log_gdp=ln(gdp)
reghdfe importers log_dist log_gdp, a(firmid) cluster(firmid iso)
 capture drop samp_imp
 gen samp_imp=e(sample)
 
reghdfe exporters log_dist log_gdp , a(firmid) cluster(firmid iso)
 capture drop samp_exp
 gen samp_exp=e(sample) 
 
*drop countries that are not in the analysis
bys iso: gen country_import_count=sum(samp_imp)
bys iso: gen country_export_count=sum(samp_exp)

drop if country_import_count==0 | country_export_count==0

*drop countries that only have one importer or exporter
bys iso3: egen tot_importers=sum(importers)
bys iso3: egen tot_exporters=sum(exporters)

tab iso3 if tot_importers==0
tab iso3 if tot_exporters==0

drop if tot_importers==1 | tot_exporters==1

foreach flow in imp exp {
	 *count of total countries
	  bys firmid: egen tot_`flow'_countries=sum(`flow'orters)
	  replace tot_`flow'_countries=0 if tot_`flow'_countries==.
	  }
 
gen samp_icheck= ( tot_imp_countries>1 )
gen samp_echeck= ( tot_exp_countries>1 )

tab samp_imp samp_icheck, miss
tab samp_exp samp_echeck, miss

replace samp_imp=0 if samp_icheck==0
replace samp_exp=0 if samp_echeck==0



* Importer regressions
****************************************
*extensive margin
reghdfe importers assemblers for region_assemblers region_for  if samp_imp==1, a(firmid iso) cluster(firmid iso)
   do "$disclosure/count_regs.do"
   estimates store imp_ext3
   gen samp_imp_ext=e(sample)
 
reghdfe importers assemblers for region_assemblers region_for exporters region_exporters  if samp_imp==1, a(firmid iso) cluster(firmid iso)
   do "$disclosure/count_regs.do"
   estimates store imp_ext4
   
reghdfe importers assemblers for region_assemblers region_for exporters region_exporters region_importers_imp  if samp_imp==1, a(firmid iso) cluster(firmid iso)
   do "$disclosure/count_regs.do"
   estimates store imp_ext5   
   
*intensive margin
reghdfe log_imports assemblers for region_assemblers region_for  if samp_imp==1, a(firmid iso) cluster(firmid iso)
   do "$disclosure/count_regs.do"
   estimates store imp_int3
 
reghdfe log_imports assemblers for region_assemblers region_for exporters region_exporters if samp_imp==1, a(firmid iso) cluster(firmid iso)
   do "$disclosure/count_regs.do"
   estimates store imp_int4  
   
reghdfe log_imports assemblers for region_assemblers region_for exporters region_exporters region_importers_imp  if samp_imp==1, a(firmid iso) cluster(firmid iso)
   do "$disclosure/count_regs.do"
   estimates store imp_int5

* Exporter regressions
****************************************
*extensive margin
reghdfe exporters assemblers for region_assemblers region_for   if samp_exp==1, a(firmid iso) cluster(firmid iso)
   do "$disclosure/count_regs.do"
   estimates store exp_ext3
   gen samp_exp_ext=e(sample)
  
reghdfe exporters assemblers for region_assemblers region_for importers region_importers   if samp_exp==1, a(firmid iso) cluster(firmid iso)
   do "$disclosure/count_regs.do"
   estimates store exp_ext4
   
reghdfe exporters assemblers for region_assemblers region_for importers region_exporters_exp region_importers   if samp_exp==1, a(firmid iso) cluster(firmid iso)
   do "$disclosure/count_regs.do"
   estimates store exp_ext5

*intensive margin
reghdfe log_exports assemblers for region_assemblers region_for   if samp_exp==1, a(firmid iso) cluster(firmid iso)
   do "$disclosure/count_regs.do"
   estimates store exp_int3
 
reghdfe log_exports assemblers for region_assemblers region_for importers region_importers   if samp_exp==1, a(firmid iso) cluster(firmid iso)
   do "$disclosure/count_regs.do"
   estimates store exp_int4
   
reghdfe log_exports assemblers for region_assemblers region_for importers region_exporters_exp region_importers   if samp_exp==1, a(firmid iso) cluster(firmid iso)
   do "$disclosure/count_regs.do"
   estimates store exp_int5
   
estfe . imp* exp*, labels(firmid "Firm FEs" iso "Country FEs")
return list

save $data/temp.dta, replace 


 esttab imp_ext3 imp_ext4 imp_ext5 imp_int3 imp_int4 imp_int5 ///
   using "$output/TABLE_3.xls", replace title("Import Regressions") ///
   cells(b(star fmt(%9.3f)) se(par))  drop(_cons) ///
   order(assemblers for region_assemblers region_for ///
	exporters region_exporters  region_importers_imp ) ///
   stats(clustvar r2_a N N_round export_mean export_sd , fmt(%12s %9.3f %9.0fc %9.0fc %9.3f %9.3f) ///
   labels("Clustered by" "Adj. R2" "Observations" "Observations" "Dep Var Mean" "Dep Var SD")) ///
   indicate("Firm FEs=0.firmid" "Country FEs=0.iso3") ///
   starlevels(* 0.10 ** 0.05 *** 0.01) label tab  
   
 esttab exp_ext3 exp_ext4 exp_ext5 exp_int3 exp_int4 exp_int5 ///
   using "$output/TABLE_3.xls", append title("Export Regressions") ///
   cells(b(star fmt(%9.3f)) se(par)) drop(_cons)  ///
   order(assemblers for region_assemblers region_for)  ///
   stats(clustvar r2_a N N_round export_mean export_sd , fmt(%12s %9.3f %9.0fc %9.0fc %9.3f %9.3f) ///
   labels("Clustered by" "Adj. R2" "Observations" "Observations" "Dep Var Mean" "Dep Var SD")) ///
   indicate("Firm FEs=0.firmid" "Country FEs=0.iso3") ///
   starlevels(* 0.10 ** 0.05 *** 0.01) label tab  
   
   
   
********Theory-based regressions***************

*Exclude foreign-owned firms...don't observe affiliates   
   
gen t_regs_imp=1 if mne_status~="FOR"   & samp_imp==1
gen t_regs_exp=1 if mne_status~="FOR"   & samp_exp==1



tab for, miss  //  

// No. of affiliates and theory distance measures  //



	  
* Importer regressions
****************************************
*extensive margin

*simple gravity regressions
reghdfe importers assemblers aff0_500 aff501_1000 aff1001_2000 aff2001_4000 aff4001 ///
	if t_regs_imp==1  , a(firmid iso) cluster(firmid iso)
	   do "$disclosure/count_regs.do"
   estimates store imp_ext_g9
   
reghdfe importers assemblers y_phi_i_fta y_phi_i_leg y_phi_i_lan y_phi_i_con ///
	if t_regs_imp==1  , a(firmid iso) cluster(firmid iso)
	   do "$disclosure/count_regs.do"
   estimates store imp_ext_g8
     
reghdfe importers assemblers y_phi_i_fta y_phi_i_leg y_phi_i_lan aff0_500 aff501_1000 aff1001_2000 aff2001_4000 aff4001 ///
	if t_regs_imp==1  , a(firmid iso) cluster(firmid iso)
	   do "$disclosure/count_regs.do"
   estimates store imp_ext_g10
     
  
   
*intensive margin
*simple gravity regressions
   
reghdfe log_imports assemblers aff0_500 aff501_1000 aff1001_2000 aff2001_4000 aff4001 ///
	if t_regs_imp==1  , a(firmid iso) cluster(firmid iso)
	   do "$disclosure/count_regs.do"
   estimates store imp_int_g9
   
reghdfe log_imports assemblers y_phi_i_fta y_phi_i_leg y_phi_i_lan y_phi_i_con ///
	if t_regs_imp==1  , a(firmid iso) cluster(firmid iso)
	   do "$disclosure/count_regs.do"
   estimates store imp_int_g8

reghdfe log_imports assemblers aff0_500 aff501_1000 aff1001_2000 aff2001_4000 aff4001 y_phi_i_fta y_phi_i_leg y_phi_i_lan ///
	if t_regs_imp==1  , a(firmid iso) cluster(firmid iso)
	   do "$disclosure/count_regs.do"
   estimates store imp_int_g10
  
 
*******************************************   
* Exporter regressions
****************************************
*extensive margin

*simple gravity regressions
reghdfe exporters assemblers aff0_500 aff501_1000 aff1001_2000 aff2001_4000 aff4001 ///
	if t_regs_exp==1 , a(firmid iso) cluster(firmid iso)
	   do "$disclosure/count_regs.do"
   estimates store exp_ext_g9
   
reghdfe exporters assemblers x_phi_i_fta x_phi_i_leg x_phi_i_lan x_phi_i_con ///
	if t_regs_exp==1, a(firmid iso) cluster(firmid iso)
	   do "$disclosure/count_regs.do"
   estimates store exp_ext_g8
     
reghdfe exporters assemblers aff0_500 aff501_1000 aff1001_2000 aff2001_4000 aff4001 x_phi_i_fta x_phi_i_leg x_phi_i_lan ///
	if t_regs_exp==1 , a(firmid iso) cluster(firmid iso)
	   do "$disclosure/count_regs.do"
   estimates store exp_ext_g10 


*intensive margin
*simple gravity regressions
reghdfe log_exports assemblers aff0_500 aff501_1000 aff1001_2000 aff2001_4000 aff4001 ///
	if t_regs_exp==1 , a(firmid iso) cluster(firmid iso)
	   do "$disclosure/count_regs.do"
   estimates store exp_int_g9
   
reghdfe log_exports assemblers x_phi_i_fta x_phi_i_leg x_phi_i_lan x_phi_i_con ///
	if t_regs_exp==1 , a(firmid iso) cluster(firmid iso)
	   do "$disclosure/count_regs.do"
   estimates store exp_int_g8
       
reghdfe log_exports assemblers aff0_500 aff501_1000 aff1001_2000 aff2001_4000 aff4001 x_phi_i_fta x_phi_i_leg x_phi_i_lan ///
	if t_regs_exp==1 , a(firmid iso) cluster(firmid iso)
	   do "$disclosure/count_regs.do"
   estimates store exp_int_g10
   
   

   
      estfe . imp* exp*, labels(firmid "Firm FEs" iso "Country FEs")
return list

save $data/trade_regs_data.dta, replace  //  use for disclosure statistics

*Imports   

*gravity regressions

*TABLE 4
esttab 	imp_ext_g9 imp_ext_g8 imp_ext_g10 ///
	exp_ext_g9 exp_ext_g8 exp_ext_g10 ///
	using "$output/TABLE_4.xls", ///
   replace title("Gravity Extensive Margin Regressions") ///
   cells(b(star fmt(%9.3f)) se(par))  drop(_cons) ///
   order(assemblers ) ///
   stats(clustvar r2_a N N_round import_mean import_sd , fmt(%12s %9.3f %9.0fc %9.0fc %9.3f %9.3f) ///
   labels("Clustered by" "Adj. R2" "Observations" "Observations" "Dep Var Mean" "Dep Var SD")) ///
   indicate("Firm FEs=0.firmid" "Country FEs=0.iso3") ///
   starlevels(* 0.10 ** 0.05 *** 0.01) label tab  

 

*APPENDIX TABLE A9
esttab 	imp_int_g9 imp_int_g8 imp_int_g10 ///
	exp_int_g9 exp_int_g8 exp_int_g10 ///
	using "$output/TABLE_A9.xls", ///
   replace title("Gravity Intenive Margin Regressions") ///
   cells(b(star fmt(%9.3f)) se(par))  drop(_cons) ///
   order(assemblers ) ///
   stats(clustvar r2_a N N_round export_mean export_sd , fmt(%12s %9.3f %9.0fc %9.0fc %9.3f %9.3f) ///
   labels("Clustered by" "Adj. R2" "Observations" "Observations" "Dep Var Mean" "Dep Var SD")) ///
   indicate("Firm FEs=0.firmid" "Country FEs=0.iso3") ///
   starlevels(* 0.10 ** 0.05 *** 0.01) label tab  
 


*****Summary stats for the regressions***************
use $data/trade_regs_data.dta, clear

summ importers if samp_imp_ext==1
summ exporters if samp_exp_ext==1

*sample of firms for the gravity regressions
**********************************************************
*imports
use $data/trade_regs_data.dta, clear
keep if t_regs_imp==1
collapse (mean) y_phi_i_harm  y_phi_i_fta  y_phi_i_leg y_phi_i_lan y_phi_i_con ///
		log_num_maff log_num_exp log_num_imp assemblers importers log_imports
gen stat="mean"
gen sample="gravity import regressions"

order sample stat
save $data/summ_stats.dta, replace	

use $data/trade_regs_data.dta, clear
keep if t_regs_imp==1
collapse (sd) y_phi_i_harm  y_phi_i_fta  y_phi_i_leg y_phi_i_lan y_phi_i_con ///
		log_num_maff log_num_exp log_num_imp assemblers importers log_imports
gen stat="std dev"
gen sample="gravity import regressions"

order sample stat
merge 1:1 y_phi_i_harm  y_phi_i_fta  y_phi_i_leg y_phi_i_lan y_phi_i_con ///
		log_num_maff log_num_exp log_num_imp assemblers importers log_imports ///
		using $data/summ_stats.dta
		
do $disc_programs/rounding_4sigdig_v2.do y_phi_i_harm  y_phi_i_fta  y_phi_i_leg y_phi_i_lan y_phi_i_con
do $disc_programs/rounding_4sigdig_v2.do log_num_maff log_num_exp log_num_imp assemblers importers log_imports

drop _merge
save $data/summ_stats_imp.dta, replace	

*Section 3.2: Mean share of importer-country cells with positive importers ("importers")
format  y_phi_i_harm  y_phi_i_fta  y_phi_i_leg y_phi_i_lan y_phi_i_con log_num_maff log_num_exp log_num_imp assemblers importers log_imports  %9.4fc
export excel using $output/afft_restat_01.xls, sheetreplace sheet(trade_regs_summ_stats_imports) firstrow(variables)

*exports
use $data/trade_regs_data.dta, clear
keep if t_regs_exp==1
collapse (mean) x_phi_i_harm  x_phi_i_fta  x_phi_i_leg x_phi_i_lan x_phi_i_con ///
		log_num_maff log_num_exp log_num_imp assemblers exporters log_exports
gen stat="mean"
gen sample="gravity export regressions"

order sample stat
save $data/summ_stats_exp.dta, replace	

use $data/trade_regs_data.dta, clear
keep if t_regs_exp==1
collapse (sd)  x_phi_i_harm  x_phi_i_fta  x_phi_i_leg x_phi_i_lan x_phi_i_con ///
		log_num_maff log_num_exp log_num_imp assemblers exporters  log_exports
gen stat="std dev"
gen sample="gravity export regressions"

order sample stat
merge 1:1  x_phi_i_harm  x_phi_i_fta  x_phi_i_leg x_phi_i_lan x_phi_i_con ///
		log_num_maff log_num_exp log_num_imp assemblers exporters  log_exports ///
		using $data/summ_stats_exp.dta
		
do $disc_programs/rounding_4sigdig_v2.do x_phi_i_harm  x_phi_i_fta  x_phi_i_leg x_phi_i_lan x_phi_i_con
do $disc_programs/rounding_4sigdig_v2.do log_num_maff log_num_exp log_num_imp assemblers exporters  log_exports

drop _merge
format  x_phi_i_harm  x_phi_i_fta  x_phi_i_leg x_phi_i_lan x_phi_i_con log_num_maff log_num_exp log_num_imp assemblers exporters  log_exports %9.4fc

*Output means of traders for the text (Section 3.2)
export excel using $output/afft_restat_01.xls, sheetreplace sheet(trade_regs_summ_stats_exports) firstrow(variables)



